/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     27.11.2009 9:18:29                           */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BusinessCard') and o.name = 'FK_BCARD_REF_CARDGROU')
alter table BusinessCard
   drop constraint FK_BCARD_REF_CARDGROU
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BusinessCard') and o.name = 'FK_BCARD_REF_COMP')
alter table BusinessCard
   drop constraint FK_BCARD_REF_COMP
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BusinessCardToBackFace') and o.name = 'FK_BCARDBF_REF_BCARD')
alter table BusinessCardToBackFace
   drop constraint FK_BCARDBF_REF_BCARD
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BusinessCardToBackFace') and o.name = 'FK_BCARD_REF_BACKFACE')
alter table BusinessCardToBackFace
   drop constraint FK_BCARD_REF_BACKFACE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BusinessCardToDataTemplate') and o.name = 'FK_BCDATMP_REF_BCARD')
alter table BusinessCardToDataTemplate
   drop constraint FK_BCDATMP_REF_BCARD
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BusinessCardToDataTemplate') and o.name = 'FK_BCDATMP_REF_CDATATMP')
alter table BusinessCardToDataTemplate
   drop constraint FK_BCDATMP_REF_CDATATMP
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BusinessCardToPaperType') and o.name = 'FK_BCPAPER_REF_BCARD')
alter table BusinessCardToPaperType
   drop constraint FK_BCPAPER_REF_BCARD
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BusinessCardToPaperType') and o.name = 'FK_BCPAPER_REF_PAPERTYP')
alter table BusinessCardToPaperType
   drop constraint FK_BCPAPER_REF_PAPERTYP
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CardPackage') and o.name = 'FK_CARDPACK_REF_BACKFC')
alter table CardPackage
   drop constraint FK_CARDPACK_REF_BACKFC
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CardPackage') and o.name = 'FK_CARDPACK_REF_CARDINFO')
alter table CardPackage
   drop constraint FK_CARDPACK_REF_CARDINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CardPackage') and o.name = 'FK_CARDPACK_REF_PACK')
alter table CardPackage
   drop constraint FK_CARDPACK_REF_PACK
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CardPackage') and o.name = 'FK_CARDPACK_REF_PAPERTP')
alter table CardPackage
   drop constraint FK_CARDPACK_REF_PAPERTP
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Customer') and o.name = 'FK_CUST_REF_ASP_USER')
alter table Customer
   drop constraint FK_CUST_REF_ASP_USER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('LocalizedMessage') and o.name = 'FK_LOCMSG_REF_LANG')
alter table LocalizedMessage
   drop constraint FK_LOCMSG_REF_LANG
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('"Order"') and o.name = 'FK_ORDER_REF_SHIPPING_ADR')
alter table "Order"
   drop constraint FK_ORDER_REF_SHIPPING_ADR
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('OrderHistory') and o.name = 'FK_ORDHIST_REF_ORDER')
alter table OrderHistory
   drop constraint FK_ORDHIST_REF_ORDER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Package') and o.name = 'FK_PACK_REF_CUST')
alter table Package
   drop constraint FK_PACK_REF_CUST
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Package') and o.name = 'FK_PACK_REF_ORDER')
alter table Package
   drop constraint FK_PACK_REF_ORDER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Package') and o.name = 'FK_PACK_REF_PRICEGRO')
alter table Package
   drop constraint FK_PACK_REF_PRICEGRO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Package') and o.name = 'FK_PACK_REF_PRODUCT')
alter table Package
   drop constraint FK_PACK_REF_PRODUCT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Product') and o.name = 'FK_COMP_REF_PROD_TYP')
alter table Product
   drop constraint FK_COMP_REF_PROD_TYP
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Product') and o.name = 'FK_PROD_REF_BINDUSTRY')
alter table Product
   drop constraint FK_PROD_REF_BINDUSTRY
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Product') and o.name = 'FK_PROD_REF_BSTYLE')
alter table Product
   drop constraint FK_PROD_REF_BSTYLE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ProductToPriceGroup') and o.name = 'FK_CMPPRG_REF_COMP')
alter table ProductToPriceGroup
   drop constraint FK_CMPPRG_REF_COMP
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ProductToPriceGroup') and o.name = 'FK_CMP_REF_PRICEGRO')
alter table ProductToPriceGroup
   drop constraint FK_CMP_REF_PRICEGRO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TagToComponent') and o.name = 'FK_TAGTOCOM_REF_PROD')
alter table TagToComponent
   drop constraint FK_TAGTOCOM_REF_PROD
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TagToComponent') and o.name = 'FK_TAGTOCOM_REF_TAG')
alter table TagToComponent
   drop constraint FK_TAGTOCOM_REF_TAG
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('BackFace')
            and   name  = 'BACK_FACE_NAME_UNIDX'
            and   indid > 0
            and   indid < 255)
   drop index BackFace.BACK_FACE_NAME_UNIDX
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BackFace')
            and   type = 'U')
   drop table BackFace
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BusinessCard')
            and   type = 'U')
   drop table BusinessCard
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BusinessCardToBackFace')
            and   type = 'U')
   drop table BusinessCardToBackFace
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BusinessCardToDataTemplate')
            and   type = 'U')
   drop table BusinessCardToDataTemplate
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BusinessCardToPaperType')
            and   type = 'U')
   drop table BusinessCardToPaperType
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CardDataTemplate')
            and   name  = 'CDATA_TEMPL_NAME_UNIDX'
            and   indid > 0
            and   indid < 255)
   drop index CardDataTemplate.CDATA_TEMPL_NAME_UNIDX
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CardDataTemplate')
            and   type = 'U')
   drop table CardDataTemplate
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CardGroup')
            and   name  = 'CARD_GROUP_NAME_UNIDX'
            and   indid > 0
            and   indid < 255)
   drop index CardGroup.CARD_GROUP_NAME_UNIDX
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CardGroup')
            and   type = 'U')
   drop table CardGroup
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CardInfoData')
            and   type = 'U')
   drop table CardInfoData
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CardPackage')
            and   name  = 'CPACK_BACKFAC_IDX'
            and   indid > 0
            and   indid < 255)
   drop index CardPackage.CPACK_BACKFAC_IDX
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CardPackage')
            and   name  = 'CPACK_PAPER_TYP_IDX'
            and   indid > 0
            and   indid < 255)
   drop index CardPackage.CPACK_PAPER_TYP_IDX
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CardPackage')
            and   name  = 'CPACK_INFODATA_IDX'
            and   indid > 0
            and   indid < 255)
   drop index CardPackage.CPACK_INFODATA_IDX
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CardPackage')
            and   type = 'U')
   drop table CardPackage
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Customer')
            and   type = 'U')
   drop table Customer
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Industry')
            and   name  = 'BINDUSTRY_UNIDX'
            and   indid > 0
            and   indid < 255)
   drop index Industry.BINDUSTRY_UNIDX
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Industry')
            and   type = 'U')
   drop table Industry
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Language')
            and   type = 'U')
   drop table Language
go

if exists (select 1
            from  sysobjects
           where  id = object_id('LocalizedMessage')
            and   type = 'U')
   drop table LocalizedMessage
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('"Order"')
            and   name  = 'ORDER_DATE_IDX'
            and   indid > 0
            and   indid < 255)
   drop index "Order".ORDER_DATE_IDX
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('"Order"')
            and   name  = 'ORDER_SHIPPING_IDX'
            and   indid > 0
            and   indid < 255)
   drop index "Order".ORDER_SHIPPING_IDX
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('"Order"')
            and   name  = 'ORDER_STATUS_IDX'
            and   indid > 0
            and   indid < 255)
   drop index "Order".ORDER_STATUS_IDX
go

if exists (select 1
            from  sysobjects
           where  id = object_id('"Order"')
            and   type = 'U')
   drop table "Order"
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('OrderHistory')
            and   name  = 'ORDER_HIST_STSDATE_IDX'
            and   indid > 0
            and   indid < 255)
   drop index OrderHistory.ORDER_HIST_STSDATE_IDX
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('OrderHistory')
            and   name  = 'ORDER_HIST_ORDER_IDX'
            and   indid > 0
            and   indid < 255)
   drop index OrderHistory.ORDER_HIST_ORDER_IDX
go

if exists (select 1
            from  sysobjects
           where  id = object_id('OrderHistory')
            and   type = 'U')
   drop table OrderHistory
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Package')
            and   name  = 'PACK_PRICE_GROP_IDX'
            and   indid > 0
            and   indid < 255)
   drop index Package.PACK_PRICE_GROP_IDX
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Package')
            and   name  = 'PACK_PROD_IDX'
            and   indid > 0
            and   indid < 255)
   drop index Package.PACK_PROD_IDX
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Package')
            and   name  = 'PACKAGE_CUSTOMER_IDX'
            and   indid > 0
            and   indid < 255)
   drop index Package.PACKAGE_CUSTOMER_IDX
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Package')
            and   name  = 'PACKAGE_ORDER_IDX'
            and   indid > 0
            and   indid < 255)
   drop index Package.PACKAGE_ORDER_IDX
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Package')
            and   type = 'U')
   drop table Package
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('PaperType')
            and   name  = 'PAPER_TYP_DESC_UNIDX'
            and   indid > 0
            and   indid < 255)
   drop index PaperType.PAPER_TYP_DESC_UNIDX
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('PaperType')
            and   name  = 'PAPER_TYP_NAME_UNIDX'
            and   indid > 0
            and   indid < 255)
   drop index PaperType.PAPER_TYP_NAME_UNIDX
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PaperType')
            and   type = 'U')
   drop table PaperType
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('PriceGroup')
            and   name  = 'PRC_GRP_ACTIVE_IDX'
            and   indid > 0
            and   indid < 255)
   drop index PriceGroup.PRC_GRP_ACTIVE_IDX
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PriceGroup')
            and   type = 'U')
   drop table PriceGroup
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Product')
            and   name  = 'PRODUCT_STYLE_IDX'
            and   indid > 0
            and   indid < 255)
   drop index Product.PRODUCT_STYLE_IDX
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Product')
            and   name  = 'PRODUCT_INDUSTRY_IDX'
            and   indid > 0
            and   indid < 255)
   drop index Product.PRODUCT_INDUSTRY_IDX
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Product')
            and   name  = 'PROD_PROD_TYP_IDX'
            and   indid > 0
            and   indid < 255)
   drop index Product.PROD_PROD_TYP_IDX
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Product')
            and   type = 'U')
   drop table Product
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ProductToPriceGroup')
            and   type = 'U')
   drop table ProductToPriceGroup
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ProductType')
            and   name  = 'PROD_TYP_NAME_UNIDX'
            and   indid > 0
            and   indid < 255)
   drop index ProductType.PROD_TYP_NAME_UNIDX
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ProductType')
            and   type = 'U')
   drop table ProductType
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ShippingAddress')
            and   name  = 'SHIPP_ADDR_EMAIL_IDX'
            and   indid > 0
            and   indid < 255)
   drop index ShippingAddress.SHIPP_ADDR_EMAIL_IDX
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ShippingAddress')
            and   name  = 'SHIPP_ADDR_MOB_IDX'
            and   indid > 0
            and   indid < 255)
   drop index ShippingAddress.SHIPP_ADDR_MOB_IDX
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ShippingAddress')
            and   name  = 'SHIPP_ADDR_TEL_IDX'
            and   indid > 0
            and   indid < 255)
   drop index ShippingAddress.SHIPP_ADDR_TEL_IDX
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ShippingAddress')
            and   type = 'U')
   drop table ShippingAddress
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Style')
            and   name  = 'BSTYLE_NAME_UNIDX'
            and   indid > 0
            and   indid < 255)
   drop index Style.BSTYLE_NAME_UNIDX
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Style')
            and   type = 'U')
   drop table Style
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Tag')
            and   name  = 'TAG_NAME_UNIDX'
            and   indid > 0
            and   indid < 255)
   drop index Tag.TAG_NAME_UNIDX
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Tag')
            and   type = 'U')
   drop table Tag
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TagToComponent')
            and   type = 'U')
   drop table TagToComponent
go

/*==============================================================*/
/* Table: BackFace                                              */
/*==============================================================*/
create table BackFace (
   BackFaceId           bigint               identity,
   LocaleNameKey        varchar(255)         not null,
   ImageDataPath        varchar(255)         not null,
   PriceRate            float                not null,
   constraint PK_BACKFACE primary key (BackFaceId)
)
go

/*==============================================================*/
/* Index: BACK_FACE_NAME_UNIDX                                  */
/*==============================================================*/
create unique index BACK_FACE_NAME_UNIDX on BackFace (
LocaleNameKey ASC
)
go

/*==============================================================*/
/* Table: BusinessCard                                          */
/*==============================================================*/
create table BusinessCard (
   ProductId            bigint               not null,
   LocaleNameKey        varchar(255)         not null,
   Type                 char(4)              not null,
   CardGroupId          int                  null,
   CreateDate           datetime             not null,
   ImageDataPath        varchar(255)         not null,
   constraint PK_BUSINESSCARD primary key (ProductId)
)
go

/*==============================================================*/
/* Table: BusinessCardToBackFace                                */
/*==============================================================*/
create table BusinessCardToBackFace (
   BusinessCardId       bigint               not null,
   BackFaceId           bigint               not null,
   constraint PK_BUSINESSCARDTOBACKFACE primary key (BusinessCardId, BackFaceId)
)
go

/*==============================================================*/
/* Table: BusinessCardToDataTemplate                            */
/*==============================================================*/
create table BusinessCardToDataTemplate (
   DataTemplateId       int                  not null,
   BusinessCardId       bigint               not null,
   constraint PK_BUSINESSCARDTODATATEMPLATE primary key (DataTemplateId, BusinessCardId)
)
go

/*==============================================================*/
/* Table: BusinessCardToPaperType                               */
/*==============================================================*/
create table BusinessCardToPaperType (
   BusinessCardId       bigint               not null,
   PaperTypeId          int                  not null,
   constraint PK_BUSINESSCARDTOPAPERTYPE primary key (BusinessCardId, PaperTypeId)
)
go

/*==============================================================*/
/* Table: CardDataTemplate                                      */
/*==============================================================*/
create table CardDataTemplate (
   DataTemplateId       int                  identity,
   TemplateXML          xml                  not null,
   LocaleNameKey        varchar(255)         not null,
   constraint PK_CARDDATATEMPLATE primary key (DataTemplateId)
)
go

/*==============================================================*/
/* Index: CDATA_TEMPL_NAME_UNIDX                                */
/*==============================================================*/
create unique index CDATA_TEMPL_NAME_UNIDX on CardDataTemplate (
LocaleNameKey ASC
)
go

/*==============================================================*/
/* Table: CardGroup                                             */
/*==============================================================*/
create table CardGroup (
   CardGroupId          int                  not null,
   LocaleNameKey        varchar(255)         not null,
   constraint PK_CARDGROUP primary key (CardGroupId)
)
go

/*==============================================================*/
/* Index: CARD_GROUP_NAME_UNIDX                                 */
/*==============================================================*/
create unique index CARD_GROUP_NAME_UNIDX on CardGroup (
LocaleNameKey ASC
)
go

/*==============================================================*/
/* Table: CardInfoData                                          */
/*==============================================================*/
create table CardInfoData (
   InfoDataId           bigint               identity,
   DataXML              xml                  not null,
   CreateDate           datetime             not null,
   LastModified         timestamp            not null,
   constraint PK_CARDINFODATA primary key (InfoDataId)
)
go

/*==============================================================*/
/* Table: CardPackage                                           */
/*==============================================================*/
create table CardPackage (
   PackageId            bigint               not null,
   BackFaceId           bigint               not null,
   PaperTypeId          int                  not null,
   InfoDataId           bigint               not null,
   Quantity             int                  not null,
   ImageDataPath        varchar(255)         null,
   constraint PK_CARDPACKAGE primary key (PackageId)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   'Child of Package, entity for one card package ',
   'user', @CurrentUser, 'table', 'CardPackage'
go

/*==============================================================*/
/* Index: CPACK_INFODATA_IDX                                    */
/*==============================================================*/
create index CPACK_INFODATA_IDX on CardPackage (
InfoDataId ASC
)
go

/*==============================================================*/
/* Index: CPACK_PAPER_TYP_IDX                                   */
/*==============================================================*/
create index CPACK_PAPER_TYP_IDX on CardPackage (
PaperTypeId ASC
)
go

/*==============================================================*/
/* Index: CPACK_BACKFAC_IDX                                     */
/*==============================================================*/
create index CPACK_BACKFAC_IDX on CardPackage (
BackFaceId ASC
)
go

/*==============================================================*/
/* Table: Customer                                              */
/*==============================================================*/
create table Customer (
   CustomerId           uniqueidentifier     not null,
   UserId               uniqueidentifier     null,
   FullName             nvarchar(100)        null,
   LastActivityDate     datetime             null,
   constraint PK_CUSTOMER primary key (CustomerId)
)
go

/*==============================================================*/
/* Table: Industry                                              */
/*==============================================================*/
create table Industry (
   IndustryId           int                  not null,
   LocaleNameKey        varchar(255)         not null,
   constraint PK_INDUSTRY primary key (IndustryId)
)
go

/*==============================================================*/
/* Index: BINDUSTRY_UNIDX                                       */
/*==============================================================*/
create unique index BINDUSTRY_UNIDX on Industry (
LocaleNameKey ASC
)
go

/*==============================================================*/
/* Table: Language                                              */
/*==============================================================*/
create table Language (
   Code                 char(3)              not null,
   Name                 nvarchar(50)         not null,
   FlagImagePath        varchar(255)         not null,
   constraint PK_LANGUAGE primary key (Code)
)
go

/*==============================================================*/
/* Table: LocalizedMessage                                      */
/*==============================================================*/
create table LocalizedMessage (
   LanguageCode         char(3)              not null,
   "Key"                varchar(255)         not null,
   Message              nvarchar(255)        not null,
   constraint PK_LOCALIZEDMESSAGE primary key (LanguageCode, "Key")
)
go

/*==============================================================*/
/* Table: "Order"                                               */
/*==============================================================*/
create table "Order" (
   OrderId              bigint               identity,
   ShippingAddressId    bigint               not null,
   Status               char(3)              not null,
   OrderDate            datetime             not null,
   constraint PK_ORDER primary key (OrderId)
)
go

/*==============================================================*/
/* Index: ORDER_STATUS_IDX                                      */
/*==============================================================*/
create index ORDER_STATUS_IDX on "Order" (
Status ASC
)
go

/*==============================================================*/
/* Index: ORDER_SHIPPING_IDX                                    */
/*==============================================================*/
create index ORDER_SHIPPING_IDX on "Order" (
ShippingAddressId ASC
)
go

/*==============================================================*/
/* Index: ORDER_DATE_IDX                                        */
/*==============================================================*/
create index ORDER_DATE_IDX on "Order" (
OrderDate DESC
)
go

/*==============================================================*/
/* Table: OrderHistory                                          */
/*==============================================================*/
create table OrderHistory (
   OrderHistoryId       bigint               identity,
   OrderId              bigint               not null,
   Status               char(3)              not null,
   StatusDate           datetime             not null,
   ModifierUserId       uniqueidentifier     not null,
   constraint PK_ORDERHISTORY primary key (OrderHistoryId)
)
go

/*==============================================================*/
/* Index: ORDER_HIST_ORDER_IDX                                  */
/*==============================================================*/
create index ORDER_HIST_ORDER_IDX on OrderHistory (
OrderId ASC
)
go

/*==============================================================*/
/* Index: ORDER_HIST_STSDATE_IDX                                */
/*==============================================================*/
create index ORDER_HIST_STSDATE_IDX on OrderHistory (
StatusDate DESC
)
go

/*==============================================================*/
/* Table: Package                                               */
/*==============================================================*/
create table Package (
   PackageId            bigint               identity,
   OrderId              bigint               null,
   CustomerId           uniqueidentifier     not null,
   ProductId            bigint               not null,
   PriceGroupId         bigint               not null,
   PrintReadyFilePath   varchar(255)         null,
   constraint PK_PACKAGE primary key (PackageId)
)
go

/*==============================================================*/
/* Index: PACKAGE_ORDER_IDX                                     */
/*==============================================================*/
create index PACKAGE_ORDER_IDX on Package (
OrderId ASC
)
go

/*==============================================================*/
/* Index: PACKAGE_CUSTOMER_IDX                                  */
/*==============================================================*/
create index PACKAGE_CUSTOMER_IDX on Package (
CustomerId ASC
)
go

/*==============================================================*/
/* Index: PACK_PROD_IDX                                         */
/*==============================================================*/
create index PACK_PROD_IDX on Package (
ProductId ASC
)
go

/*==============================================================*/
/* Index: PACK_PRICE_GROP_IDX                                   */
/*==============================================================*/
create index PACK_PRICE_GROP_IDX on Package (
PriceGroupId ASC
)
go

/*==============================================================*/
/* Table: PaperType                                             */
/*==============================================================*/
create table PaperType (
   PaperTypeId          int                  identity,
   LocaleNameKey        varchar(255)         not null,
   ImageDataPath        varchar(255)         not null,
   LocaleDescriptionKey varchar(255)         not null,
   PriceRate            float                not null,
   constraint PK_PAPERTYPE primary key (PaperTypeId)
)
go

/*==============================================================*/
/* Index: PAPER_TYP_NAME_UNIDX                                  */
/*==============================================================*/
create unique index PAPER_TYP_NAME_UNIDX on PaperType (
LocaleNameKey ASC
)
go

/*==============================================================*/
/* Index: PAPER_TYP_DESC_UNIDX                                  */
/*==============================================================*/
create unique index PAPER_TYP_DESC_UNIDX on PaperType (
LocaleDescriptionKey ASC
)
go

/*==============================================================*/
/* Table: PriceGroup                                            */
/*==============================================================*/
create table PriceGroup (
   PriceGroupId         bigint               identity,
   LocaleNameKey        varchar(255)         not null,
   Quantity             int                  not null,
   Price                money                not null,
   ActiveFrom           datetime             not null,
   ActiveTo             datetime             null,
   constraint PK_PRICEGROUP primary key (PriceGroupId)
)
go

/*==============================================================*/
/* Index: PRC_GRP_ACTIVE_IDX                                    */
/*==============================================================*/
create index PRC_GRP_ACTIVE_IDX on PriceGroup (
ActiveFrom DESC,
ActiveTo DESC
)
go

/*==============================================================*/
/* Table: Product                                               */
/*==============================================================*/
create table Product (
   ProductId            bigint               identity,
   ProductCode          char(5)              not null,
   StyleId              int                  not null,
   IndustryId           int                  not null,
   CreateDate           datetime             not null,
   constraint PK_PRODUCT primary key (ProductId)
)
go

/*==============================================================*/
/* Index: PROD_PROD_TYP_IDX                                     */
/*==============================================================*/
create index PROD_PROD_TYP_IDX on Product (
ProductCode ASC
)
go

/*==============================================================*/
/* Index: PRODUCT_INDUSTRY_IDX                                  */
/*==============================================================*/
create index PRODUCT_INDUSTRY_IDX on Product (
IndustryId ASC
)
go

/*==============================================================*/
/* Index: PRODUCT_STYLE_IDX                                     */
/*==============================================================*/
create index PRODUCT_STYLE_IDX on Product (
StyleId ASC
)
go

/*==============================================================*/
/* Table: ProductToPriceGroup                                   */
/*==============================================================*/
create table ProductToPriceGroup (
   PriceGroupId         bigint               not null,
   ProductId            bigint               not null,
   constraint PK_PRODUCTTOPRICEGROUP primary key (PriceGroupId, ProductId)
)
go

/*==============================================================*/
/* Table: ProductType                                           */
/*==============================================================*/
create table ProductType (
   Code                 char(5)              not null,
   LocaleNameKey        varchar(255)         not null,
   constraint PK_PRODUCTTYPE primary key (Code)
)
go

/*==============================================================*/
/* Index: PROD_TYP_NAME_UNIDX                                   */
/*==============================================================*/
create unique index PROD_TYP_NAME_UNIDX on ProductType (
LocaleNameKey ASC
)
go

/*==============================================================*/
/* Table: ShippingAddress                                       */
/*==============================================================*/
create table ShippingAddress (
   ShippingAddressId    bigint               identity,
   Address              nvarchar(500)        not null,
   PostalCode           varchar(20)          null,
   Tel                  varchar(100)         not null,
   Mobile               varchar(100)         null,
   Email                varchar(255)         null,
   constraint PK_SHIPPINGADDRESS primary key (ShippingAddressId)
)
go

/*==============================================================*/
/* Index: SHIPP_ADDR_TEL_IDX                                    */
/*==============================================================*/
create index SHIPP_ADDR_TEL_IDX on ShippingAddress (
Tel ASC
)
go

/*==============================================================*/
/* Index: SHIPP_ADDR_MOB_IDX                                    */
/*==============================================================*/
create index SHIPP_ADDR_MOB_IDX on ShippingAddress (
Mobile ASC
)
go

/*==============================================================*/
/* Index: SHIPP_ADDR_EMAIL_IDX                                  */
/*==============================================================*/
create index SHIPP_ADDR_EMAIL_IDX on ShippingAddress (
Email ASC
)
go

/*==============================================================*/
/* Table: Style                                                 */
/*==============================================================*/
create table Style (
   StyleId              int                  not null,
   LocaleNameKey        varchar(255)         not null,
   constraint PK_STYLE primary key (StyleId)
)
go

/*==============================================================*/
/* Index: BSTYLE_NAME_UNIDX                                     */
/*==============================================================*/
create unique index BSTYLE_NAME_UNIDX on Style (
LocaleNameKey ASC
)
go

/*==============================================================*/
/* Table: Tag                                                   */
/*==============================================================*/
create table Tag (
   TagId                bigint               not null,
   LocaleNameKey        varchar(255)         not null,
   constraint PK_TAG primary key (TagId)
)
go

/*==============================================================*/
/* Index: TAG_NAME_UNIDX                                        */
/*==============================================================*/
create unique index TAG_NAME_UNIDX on Tag (
LocaleNameKey ASC
)
go

/*==============================================================*/
/* Table: TagToComponent                                        */
/*==============================================================*/
create table TagToComponent (
   TagId                bigint               not null,
   ComponentId          bigint               not null,
   constraint PK_TAGTOCOMPONENT primary key (TagId, ComponentId)
)
go

alter table BusinessCard
   add constraint FK_BCARD_REF_CARDGROU foreign key (CardGroupId)
      references CardGroup (CardGroupId)
go

alter table BusinessCard
   add constraint FK_BCARD_REF_COMP foreign key (ProductId)
      references Product (ProductId)
go

alter table BusinessCardToBackFace
   add constraint FK_BCARDBF_REF_BCARD foreign key (BusinessCardId)
      references BusinessCard (ProductId)
go

alter table BusinessCardToBackFace
   add constraint FK_BCARD_REF_BACKFACE foreign key (BackFaceId)
      references BackFace (BackFaceId)
go

alter table BusinessCardToDataTemplate
   add constraint FK_BCDATMP_REF_BCARD foreign key (BusinessCardId)
      references BusinessCard (ProductId)
go

alter table BusinessCardToDataTemplate
   add constraint FK_BCDATMP_REF_CDATATMP foreign key (DataTemplateId)
      references CardDataTemplate (DataTemplateId)
go

alter table BusinessCardToPaperType
   add constraint FK_BCPAPER_REF_BCARD foreign key (BusinessCardId)
      references BusinessCard (ProductId)
go

alter table BusinessCardToPaperType
   add constraint FK_BCPAPER_REF_PAPERTYP foreign key (PaperTypeId)
      references PaperType (PaperTypeId)
go

alter table CardPackage
   add constraint FK_CARDPACK_REF_BACKFC foreign key (BackFaceId)
      references BackFace (BackFaceId)
go

alter table CardPackage
   add constraint FK_CARDPACK_REF_CARDINFO foreign key (InfoDataId)
      references CardInfoData (InfoDataId)
go

alter table CardPackage
   add constraint FK_CARDPACK_REF_PACK foreign key (PackageId)
      references Package (PackageId)
         on delete cascade
go

alter table CardPackage
   add constraint FK_CARDPACK_REF_PAPERTP foreign key (PaperTypeId)
      references PaperType (PaperTypeId)
go

alter table Customer
   add constraint FK_CUST_REF_ASP_USER foreign key (UserId)
      references aspnet_Users (UserId)
go

alter table LocalizedMessage
   add constraint FK_LOCMSG_REF_LANG foreign key (LanguageCode)
      references Language (Code)
go

alter table "Order"
   add constraint FK_ORDER_REF_SHIPPING_ADR foreign key (ShippingAddressId)
      references ShippingAddress (ShippingAddressId)
go

alter table OrderHistory
   add constraint FK_ORDHIST_REF_ORDER foreign key (OrderId)
      references "Order" (OrderId)
go

alter table Package
   add constraint FK_PACK_REF_CUST foreign key (CustomerId)
      references Customer (CustomerId)
go

alter table Package
   add constraint FK_PACK_REF_ORDER foreign key (OrderId)
      references "Order" (OrderId)
go

alter table Package
   add constraint FK_PACK_REF_PRICEGRO foreign key (PriceGroupId)
      references PriceGroup (PriceGroupId)
go

alter table Package
   add constraint FK_PACK_REF_PRODUCT foreign key (ProductId)
      references Product (ProductId)
go

alter table Product
   add constraint FK_COMP_REF_PROD_TYP foreign key (ProductCode)
      references ProductType (Code)
go

alter table Product
   add constraint FK_PROD_REF_BINDUSTRY foreign key (IndustryId)
      references Industry (IndustryId)
go

alter table Product
   add constraint FK_PROD_REF_BSTYLE foreign key (StyleId)
      references Style (StyleId)
go

alter table ProductToPriceGroup
   add constraint FK_CMPPRG_REF_COMP foreign key (ProductId)
      references Product (ProductId)
go

alter table ProductToPriceGroup
   add constraint FK_CMP_REF_PRICEGRO foreign key (PriceGroupId)
      references PriceGroup (PriceGroupId)
go

alter table TagToComponent
   add constraint FK_TAGTOCOM_REF_PROD foreign key (ComponentId)
      references Product (ProductId)
go

alter table TagToComponent
   add constraint FK_TAGTOCOM_REF_TAG foreign key (TagId)
      references Tag (TagId)
go

